﻿using ExcelDataReader;
using ExeclHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using UpdateData.Common;

namespace UpdateData
{
    class Program
    {
        /// <summary>
        /// 文件位置
        /// </summary>
        static string FileName = "../售前试用跟踪表.xlsx";

        /// <summary>
        /// sheet名称
        /// </summary>
        static string SheetName = "明细数据";

        /// <summary>
        /// 表格数据所在位置
        /// </summary>
        static List<string> TablePosition;

        /// <summary>
        /// 文件夹位置
        /// </summary>
        static List<LocationEntity> LocationList;

        static ReaderTableByCell readerTableByCell = new ReaderTableByCell();

        static WriterChangeCell writerChangeCell = new WriterChangeCell();

        static void Main(string[] args)
        {
            Console.WriteLine("自动更新项目数据");
            TablePosition = new List<string>();
            LocationList = new List<LocationEntity>();

            ExeclReader(FileName);
            //需要去读取的位置并转成对应的rows位置 例如 C17、D12等
            List<TableLocation> tableLocations = new List<TableLocation>();
            foreach (var tp in TablePosition)
            {
                if (string.IsNullOrEmpty(tp))
                    continue;
                var data = ExeclHeaderChange.GetHeaderValue(tp);
                TableLocation tableLocation = new TableLocation();
                tableLocation.ColumnIndex = data[0];
                tableLocation.RowIndex = data[1];
                tableLocations.Add(tableLocation);
            }
            //去读取对应的数据
            List<ChangeDateEntity> changeDateEntitys = new List<ChangeDateEntity>();
            int rowIndex = 2;//写入表的行==文件行
            foreach (var item in LocationList)
            {
                Console.WriteLine(item.FolderName + " : " + item.FileName);
                string file = "..\\" + item.FileName + ".xlsx";
                Console.WriteLine("正在更新Execl" + item.FolderName + "...");
                
               // var execlData = readerTableByCell.ExeclCellsReader(file, tableLocations);
                var execlData = writerChangeCell.ReaderExcel(file, tableLocations);

                int index = 0;
                foreach (var dt in execlData)
                {
                    if (String.IsNullOrEmpty(dt))
                    {
                        index++;
                        continue;
                    }
                    ChangeDateEntity changeDateEntity = new ChangeDateEntity();
                    changeDateEntity.ColumnIndex = index + 3;
                    changeDateEntity.RowIndex = rowIndex;
                    changeDateEntity.DataValue = dt;
                    changeDateEntitys.Add(changeDateEntity);
                    index++;
                }
                rowIndex++;
            }
            
            writerChangeCell.WriteExcel(FileName, SheetName, changeDateEntitys);
            Console.WriteLine("更新Execl数据完成");
            Console.ReadKey();
        }



        /// <summary>
        /// 读取Execl
        /// </summary>
        /// <param name="filePath"></param>
        static void ExeclReader(string filePath)
        {
            DataSet ds;
            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var extension = Path.GetExtension(filePath).ToLower();
                IExcelDataReader reader = null;
                if (extension == ".xls")
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (extension == ".xlsx")
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                else if (extension == ".csv")
                {
                    reader = ExcelReaderFactory.CreateCsvReader(stream);
                }

                if (reader == null)
                    return;

                using (reader)
                {
                    ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        UseColumnDataType = false,
                    });
                }

                var tablenames = GetTablenames(ds.Tables);
                if (ds.Tables.Count < 1)
                    return;

                //取第一个table
                var dTable = ds.Tables[0];

                int index = 0;
                foreach (DataRow row in dTable.Rows)
                {
                    if (index == 0)
                    {
                        int tempIndex = 0;
                        //获取对应表格位置
                        foreach (var item in row.ItemArray)
                        {
                            if (tempIndex <= 2)
                            {
                                tempIndex++;
                                continue;
                            }
                            TablePosition.Add(item.ToString().Trim());
                            tempIndex++;
                        }
                       
                    }
                    else if (index == 1)
                    {
                        index++;
                        continue;
                    }
                    else
                    {
                        string folderName = row.ItemArray[1].ToString().Trim();
                        string fileName = row.ItemArray[2].ToString().Trim();
                        if (!String.IsNullOrEmpty(folderName) && !String.IsNullOrEmpty(fileName))
                        {
                            LocationEntity entity = new LocationEntity();
                            entity.FolderName = folderName;
                            entity.FileName = fileName;
                            LocationList.Add(entity);
                        }
                    }
                    index++;
                }

            }
        }

        static IList<string> GetTablenames(DataTableCollection tables)
        {
            var tableList = new List<string>();
            foreach (var table in tables)
            {
                tableList.Add(table.ToString());
            }

            return tableList;
        }
    }
}
